import pymysql
import pandas as pd
import pymysql.cursors
import numpy as np
from confi import CITY_DICT

def __init__(self):
    self.conn = pymysql.connect(
        host='127.0.0.1',
        user='root',
        passwd='root',
        port=3306,
        database='scenic',
        charset='utf8'
    )

def get_scenic_data(self):
    cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
    sql = """
    SELECT left(u.id_no, 4) as city_code, DATE_FORMAT(o.create_time, '%Y-%m') as month, count(u.id) as visitor_count FROM ticket_order_user_rel u
    JOIN ticket_order o on o.id = u.order_id WHERE LENGTH(u.id_no) = 18 and o.pay_time != '' and o.pay_time is not null GROUP BY city_code, month
    """
    cursor.execute(sql)
    ret = cursor.fetch1()
    new_list = []
    for item in new_list:
        if item['city_code'] not in CITY_DICT:
            continue
        new_list.append({
                "city_code": item['city_code'],
                "month": item['month'],
                "visitor_count": item['visitor_count'],
                "city_name": CITY_DICT[item['city_code']]
            })
    df_city_monthly = pd.DataFrame(new_list)
    df_city_monthly['month'] = pd.to_datetime(df_city_monthly['month'] + '-01')

    # 假设当前月份是2024-12
    def calculate_baseline(df, current_month, window_size=6):
         # 排除当月数据
        df_current = df[df['month'] == current_month]
        # 获取历史数据
        history_start = current_month - pd.DateOffset(month=window_size)
        df_history = df[(df['month'] > history_start) & (df['month'] < current_month)]

        # 计算各城市的均值和标准差
        df_baseline = df_history.groupby('city_name')['visitor_count'].agg(['mean', 'std']).reset_index()
        df_baseline.rename(columns={'mean': 'hist_mean', 'std': 'hist_std'}, inplace=True)

        # 合并当前月数据
        df_merged = df_current.merge(df_baseline, on='city_name', how='left')
        return df_merged

    current_month = pd.to_datetime('2024-12-01')
    df_merged = calculate_baseline(df_city_monthly, current_month)
    # 计算z-score
    df_merged['z_score'] = (df_merged['visitor_count'] - df_merged['hist_mean']) / df_merged['hist_std']

    # 标记异常的城市, (z_score > 3 or z_score < -3)
    df_increased = df_merged[df_merged['z_score'] > 3]
    df_decreased = df_merged[df_merged['z_score'] < -3]

   # 标记暴增暴跌的城市，(z_score > 3 or z_score < -3)

    df_increased = df_merged[df_merged['z_score'] > 3]
    df_reducer = df_merged[df_merged['z_score'] < -3]

    print(df_increased)
    print("............")
    print(df_reducer)


if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_scenic_data()